/*
Navicat MySQL Data Transfer

Source Server         : bobjava
Source Server Version : 50560
Source Host           : localhost:3306
Source Database       : uolab3

Target Server Type    : MYSQL
Target Server Version : 50560
File Encoding         : 65001

Date: 2021-07-05 16:56:15
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `college`
-- ----------------------------
DROP TABLE IF EXISTS `college`;
CREATE TABLE `college` (
`id`  int(11) NOT NULL AUTO_INCREMENT ,
`code`  int(11) NOT NULL ,
`name`  varchar(20) NOT NULL ,
`updatetime`  datetime NOT NULL ,
PRIMARY KEY (`id`)
)
AUTO_INCREMENT=1

;

-- ----------------------------
-- Records of college
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for `competition`
-- ----------------------------
DROP TABLE IF EXISTS `competition`;
CREATE TABLE `competition` (
`id`  int(11) NOT NULL AUTO_INCREMENT ,
`category`  char(10) NOT NULL ,
`year`  int(11) NOT NULL ,
`title`  varchar(30) NOT NULL ,
`leader`  varchar(10) NOT NULL ,
`members`  varchar(50) NOT NULL ,
`level`  char(3) NOT NULL ,
`teacher`  varchar(6) NOT NULL ,
`remark`  varchar(200) NULL DEFAULT NULL ,
`updatetime`  datetime NOT NULL ,
PRIMARY KEY (`id`)
)
AUTO_INCREMENT=1

;

-- ----------------------------
-- Records of competition
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for `credits`
-- ----------------------------
DROP TABLE IF EXISTS `credits`;
CREATE TABLE `credits` (
`id`  int(11) NOT NULL AUTO_INCREMENT ,
`college`  int(11) NULL DEFAULT NULL ,
`code`  char(20) NULL DEFAULT NULL ,
`name`  varchar(20) NOT NULL ,
`grade`  int(11) NOT NULL ,
`score`  decimal(4,2) NOT NULL ,
`reason`  varchar(20) NOT NULL ,
`achieve_time`  datetime NOT NULL ,
`enter_time`  datetime NOT NULL ,
`enter_man`  varchar(6) NOT NULL ,
`remark`  varchar(200) NULL DEFAULT NULL ,
`updatetime`  datetime NOT NULL ,
PRIMARY KEY (`id`),
FOREIGN KEY (`college`) REFERENCES `college` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
)
AUTO_INCREMENT=1

;

-- ----------------------------
-- Records of credits
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for `major`
-- ----------------------------
DROP TABLE IF EXISTS `major`;
CREATE TABLE `major` (
`id`  int(11) NOT NULL AUTO_INCREMENT ,
`college`  int(11) NULL DEFAULT NULL ,
`code`  int(11) NOT NULL ,
`name`  varchar(20) NOT NULL ,
`updatetime`  datetime NOT NULL ,
PRIMARY KEY (`id`),
FOREIGN KEY (`college`) REFERENCES `college` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
)
AUTO_INCREMENT=1

;

-- ----------------------------
-- Records of major
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for `member`
-- ----------------------------
DROP TABLE IF EXISTS `member`;
CREATE TABLE `member` (
`id`  int(11) NOT NULL AUTO_INCREMENT ,
`college`  int(11) NULL DEFAULT NULL ,
`major`  int(11) NULL DEFAULT NULL ,
`code`  char(20) NOT NULL ,
`name`  char(20) NULL DEFAULT NULL ,
`sex`  int(11) NOT NULL ,
`grade`  int(11) NOT NULL ,
`native_place`  char(20) NOT NULL ,
`phone`  char(11) NOT NULL ,
`school_job`  char(5) NOT NULL ,
`join_date`  date NOT NULL ,
`state`  char(2) NOT NULL ,
`lab_job`  char(4) NOT NULL ,
`remark`  varchar(200) NULL DEFAULT NULL ,
`updatetime`  datetime NOT NULL ,
PRIMARY KEY (`id`),
FOREIGN KEY (`major`) REFERENCES `major` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
FOREIGN KEY (`college`) REFERENCES `college` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
)
AUTO_INCREMENT=1

;

-- ----------------------------
-- Records of member
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for `openclass`
-- ----------------------------
DROP TABLE IF EXISTS `openclass`;
CREATE TABLE `openclass` (
`id`  int(11) NOT NULL AUTO_INCREMENT ,
`major`  int(11) NULL DEFAULT NULL ,
`college`  int(11) NULL DEFAULT NULL ,
`class_date`  date NOT NULL ,
`subject`  varchar(50) NOT NULL ,
`speaker`  varchar(4) NOT NULL ,
`organize`  varchar(15) NOT NULL ,
`place`  varchar(20) NOT NULL ,
`group_show`  varchar(50) NOT NULL ,
`way`  char(2) NOT NULL ,
`score`  decimal(3,2) NOT NULL ,
`participants`  decimal(3,0) NOT NULL ,
`remark`  varchar(200) NULL DEFAULT NULL ,
`updatetime`  datetime NOT NULL ,
PRIMARY KEY (`id`),
FOREIGN KEY (`major`) REFERENCES `major` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
FOREIGN KEY (`college`) REFERENCES `college` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
)
AUTO_INCREMENT=1

;

-- ----------------------------
-- Records of openclass
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for `permission`
-- ----------------------------
DROP TABLE IF EXISTS `permission`;
CREATE TABLE `permission` (
`id`  int(11) NOT NULL ,
`name`  char(50) NOT NULL ,
`url`  char(150) NOT NULL ,
`parent`  int(11) NOT NULL ,
`menu`  int(11) NOT NULL ,
PRIMARY KEY (`id`)
)

;

-- ----------------------------
-- Records of permission
-- ----------------------------
BEGIN;
INSERT INTO `permission` VALUES ('1', '用户管理', '', '0', '1'), ('2', '增加用户', '', '1', '0'), ('3', '修改用户', '', '1', '0'), ('4', '删除用户', '', '1', '0'), ('5', '角色管理', '', '0', '1'), ('6', '增加角色', '', '5', '0'), ('7', '修改角色', '', '5', '0'), ('8', '删除角色', '', '5', '0'), ('9', '查询用户', '', '1', '0'), ('10', '查询角色', '', '5', '0'), ('11', '查询权限', '', '5', '0'), ('12', '院系管理', '', '0', '1'), ('13', '增加院系', '', '12', '0'), ('14', '修改院系', '', '12', '0'), ('15', '删除院系', '', '12', '0'), ('16', '查询院系', '', '12', '0'), ('17', '专业管理', '', '0', '1'), ('18', '增加专业', '', '17', '0'), ('19', '修改专业', '', '17', '0'), ('20', '删除专业', '', '17', '0'), ('21', '查询专业', '', '17', '0'), ('22', '成员管理', '', '0', '1'), ('23', '增加成员', '', '22', '0'), ('24', '修改成员', '', '22', '0'), ('25', '删除成员', '', '22', '0'), ('26', '查询成员', '', '22', '0'), ('27', '公开课管理', '', '0', '1'), ('28', '增加公开课', '', '27', '0'), ('29', '修改公开课', '', '27', '0'), ('30', '删除公开课', '', '27', '0'), ('31', '查询公开课', '', '27', '0'), ('32', '参赛管理', '', '0', '1'), ('33', '增加参赛', '', '32', '0'), ('34', '修改参数', '', '32', '0'), ('35', '删除参赛', '', '32', '0'), ('36', '查询参赛', '', '32', '0'), ('37', '创新学分管理', '', '0', '1'), ('38', '增加创新学分', '', '37', '0'), ('39', '修改创新学分', '', '37', '0'), ('40', '删除创新学分', '', '37', '0'), ('41', '查询创新学分', '', '37', '0'), ('42', '成员统计', '', '0', '1'), ('43', '登录', '', '0', '0'), ('44', '退出', '', '0', '0');
COMMIT;

-- ----------------------------
-- Table structure for `role`
-- ----------------------------
DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
`id`  int(11) NOT NULL AUTO_INCREMENT ,
`name`  char(50) NOT NULL ,
PRIMARY KEY (`id`)
)
AUTO_INCREMENT=1

;

-- ----------------------------
-- Records of role
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for `role_permission`
-- ----------------------------
DROP TABLE IF EXISTS `role_permission`;
CREATE TABLE `role_permission` (
`role`  int(11) NOT NULL ,
`permission`  int(11) NOT NULL ,
PRIMARY KEY (`role`, `permission`),
FOREIGN KEY (`role`) REFERENCES `role` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
FOREIGN KEY (`permission`) REFERENCES `permission` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
)

;

-- ----------------------------
-- Records of role_permission
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for `user`
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id`  int(11) NOT NULL AUTO_INCREMENT ,
`role`  int(11) NULL DEFAULT NULL ,
`college`  int(11) NULL DEFAULT NULL ,
`major`  int(11) NULL DEFAULT NULL ,
`username`  varchar(15) NOT NULL ,
`password`  varchar(20) NOT NULL ,
`state`  char(2) NOT NULL ,
`name`  char(5) NOT NULL ,
`grade`  int(11) NOT NULL ,
`clazz`  char(20) NOT NULL ,
`school_job`  char(5) NOT NULL ,
`phone`  char(11) NOT NULL ,
`QQ`  char(20) NOT NULL ,
`remark`  varchar(200) NULL DEFAULT NULL ,
`updatetime`  datetime NOT NULL ,
PRIMARY KEY (`id`),
FOREIGN KEY (`role`) REFERENCES `role` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
FOREIGN KEY (`college`) REFERENCES `college` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT,
FOREIGN KEY (`major`) REFERENCES `major` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
)
AUTO_INCREMENT=1

;

-- ----------------------------
-- Records of user
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Auto increment value for `college`
-- ----------------------------
ALTER TABLE `college` AUTO_INCREMENT=1;

-- ----------------------------
-- Auto increment value for `competition`
-- ----------------------------
ALTER TABLE `competition` AUTO_INCREMENT=1;

-- ----------------------------
-- Auto increment value for `credits`
-- ----------------------------
ALTER TABLE `credits` AUTO_INCREMENT=1;

-- ----------------------------
-- Auto increment value for `major`
-- ----------------------------
ALTER TABLE `major` AUTO_INCREMENT=1;

-- ----------------------------
-- Auto increment value for `member`
-- ----------------------------
ALTER TABLE `member` AUTO_INCREMENT=1;

-- ----------------------------
-- Auto increment value for `openclass`
-- ----------------------------
ALTER TABLE `openclass` AUTO_INCREMENT=1;

-- ----------------------------
-- Auto increment value for `role`
-- ----------------------------
ALTER TABLE `role` AUTO_INCREMENT=1;

-- ----------------------------
-- Auto increment value for `user`
-- ----------------------------
ALTER TABLE `user` AUTO_INCREMENT=1;
